package dao.implementations;

import dao.interfaces.InstanceDao;
import engine.Instance;
import engine.Time;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * An MS SQL data access object interface for instances.
 * @author Johnny
 */
public class InstanceDaoMsSql extends DaoMsSql implements InstanceDao {

    public ArrayList<Instance> getInstances(int unitId) {
        ArrayList<Instance> instList = new ArrayList<Instance>();
        String query = "SELECT InstanceID FROM Instances WHERE UnitID = " + unitId;
        try {
            Statement stmt = getStatement();
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                int instId = rs.getInt(1);
                instList.add(new Instance(instId, getSchedule(instId)));
            }
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(EdgeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }

        return instList;
    }

    public Instance addInstance(int unitId) {
        Instance inst = null;
        StringBuffer sb = new StringBuffer();

        sb.append("SET NOCOUNT ON\n");
        sb.append("INSERT INTO Instances ( UnitID ) ");
        sb.append("VALUES ( ");
        sb.append(unitId);
        sb.append(" )\n");
        sb.append("SELECT SCOPE_IDENTITY()");

        try {
            Statement stmt = getStatement();
            ResultSet rs = stmt.executeQuery(sb.toString());
            rs.next();

            int newId = rs.getInt(1);
            createDummySchedule(newId, getCourseNr(unitId));
            inst = new Instance(newId, getSchedule(newId));

            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(NodeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }

        return inst;
    }

    public void removeInstance(int instanceId) {
        // First we remove the schedule
        StringBuffer command = new StringBuffer();
        command.append("DELETE FROM Schedules WHERE InstanceID = ");
        command.append(instanceId);
        command.append("\n");
        // Then we remove the instance itself
        command.append("DELETE FROM Instances WHERE InstanceID = ");
        command.append(instanceId);

        try {
            Statement stmt = getStatement();
            stmt.executeUpdate(command.toString());
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(NodeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void setSchedule(Instance inst) {
        ArrayList<Time> sched = inst.getSchedules();
        StringBuffer command = new StringBuffer();
        for (int i = 0; i < sched.size(); i++) {
            command.append("UPDATE Schedules SET Time = ");
            command.append(sched.get(i).getTotalSeconds());
            command.append(" WHERE InstanceID = ");
            command.append(inst.getId());
            command.append(" AND TimeNr = ");
            command.append(i + 1);
            command.append("\n");
        }

        try {
            Statement stmt = getStatement();
            stmt.executeUpdate(command.toString());
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(NodeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Returns the schedule list of an instance.
     * @param instanceId the id of the instance who's schedule will be returned.
     * @return the schedule list of an instance
     */
    private ArrayList<Time> getSchedule(int instanceId) {
        ArrayList<Time> schedList = new ArrayList<Time>();
        StringBuffer query = new StringBuffer();
        query.append("SELECT Time FROM Schedules WHERE InstanceID = ");
        query.append(instanceId);
        query.append(" ORDER BY TimeNr");

        try {
            Statement stmt = getStatement();
            ResultSet rs = stmt.executeQuery(query.toString());
            while (rs.next()) {
                schedList.add(new Time(rs.getInt(1)));
            }
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(EdgeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }

        return schedList;
    }

    /**
     * Creates a dummy schedule for an instance.
     * @param instId the id of the instance who's schedule will be created.
     */
    private void createDummySchedule(int instId, int nr) {
        StringBuffer command = new StringBuffer();
        for (int i = 1; i <= nr; i++) {
            command.append("INSERT INTO Schedules ( InstanceID, TimeNr, Time ) ");
            command.append("VALUES ( ");
            command.append(instId);
            command.append(',');
            command.append(i);
            command.append(',');
            command.append(i);
            command.append(")\n");
        }

        try {
            Statement stmt = getStatement();
            stmt.executeUpdate(command.toString());
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(NodeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Returns the number of course nodes to estabelish a schedule.
     * @param unitId the id of the unit who's course will be checked.
     * @return the number of course nodes.
     */
    private int getCourseNr(int unitId) {
        String query = "SELECT COUNT(*) FROM Courses WHERE UnitID = " + unitId;
        int nr = 0;

        try {
            Statement stmt = getStatement();
            ResultSet rs = stmt.executeQuery(query);
            rs.next();
            nr = rs.getInt(1);
            stmt.close();
        } catch (SQLException ex) {
            Logger.getLogger(NodeDaoMsSql.class.getName()).log(Level.SEVERE, null, ex);
        }

        return nr;
    }
}
